﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Gumini.JulaDB.Provider;
using System.Data;
using System.IO;

namespace Gumini.JulaDB.Samples
{
    class Program
    {
        private static void CreateSampleSchema()
        {
            using (var connection = new JulaDbConnection(@"Data Source=C:\Databases\sample.juladb"))
            {
                connection.Open();
                var command = new JulaDbCommand(connection);
                command.CommandText =
                @"create table Customer
                (
                    CustomerId int,
                    Name varchar(100)
                );
                create table Order
                (
                    OrderId int,
                    CustomerId int
                );
                create table OrderItem
                (
                    OrderId int,
                    ProductId int,
                    Quantity decimal
                );
                create table Product
                (
                    ProductId int,
                    Name varchar(100),
                    Price decimal
                )";
                command.ExecuteNonQuery();
            }
        }

        private static void FillSampleSchema()
        {
            using (var connection = new JulaDbConnection(@"Data Source=C:\Databases\sample.juladb"))
            {
                connection.Open();
                var command = new JulaDbCommand(connection);
                command.CommandText =
                    @"insert into Customer (CustomerId, Name) values (0, 'John');
                    insert into Customer (CustomerId, Name) values (1, 'Mary');
                    insert into Customer (CustomerId, Name) values (2, 'Jonathan');
                    insert into Customer (CustomerId, Name) values (3, 'Adam');
                    insert into Customer (CustomerId, Name) values (4, 'Ann');
                    insert into Customer (CustomerId, Name) values (5, 'Tom');
                    insert into Product (ProductId, Name, Price) values (0, 'Apple', 12.2);
                    insert into Product (ProductId, Name, Price) values (1, 'Orange', 18.0);
                    insert into Product (ProductId, Name, Price) values (2, 'Blackberry', 25.99);
                    insert into Product (ProductId, Name, Price) values (3, 'Banana', 7.99);
                    insert into Order (OrderId, CustomerId) values (0, 0);
                    insert into OrderItem (OrderId, ProductId, Quantity) values (0, 0, 2);
                    insert into OrderItem (OrderId, ProductId, Quantity) values (0, 2, 1);
                    insert into Order (OrderId, CustomerId) values (1, 1);
                    insert into OrderItem (OrderId, ProductId, Quantity) values (1, 1, 1);
                    insert into Order (OrderId, CustomerId) values (2, 1);
                    insert into OrderItem (OrderId, ProductId, Quantity) values (2, 0, 2);
                    insert into OrderItem (OrderId, ProductId, Quantity) values (2, 1, 5);
                    insert into Order (OrderId, CustomerId) values (3, 5);
                    insert into OrderItem (OrderId, ProductId, Quantity) values (3, 3, 3);";
                command.ExecuteNonQuery();
            }
        }

        private static void QueryCountRows()
        {
            using (var connection = new JulaDbConnection(@"Data Source=C:\Databases\sample.juladb"))
            {
                connection.Open();
                var command = new JulaDbCommand(connection);
                command.CommandText = "select count(*) from Customer";
                decimal result = (decimal)command.ExecuteScalar();

                // wypisuje na ekran "Wynik: 6"
                Console.WriteLine("Wynik: {0}", result);
            }
        }

        private static void QueryProductPriceDataAdapter()
        {
            using (var connection = new JulaDbConnection(@"Data Source=C:\Databases\sample.juladb"))
            {
                connection.Open();
                var adapter = new JulaDbDataAdapter(
                    @"select * from Product where Price > 10 and Price < 20",
                    connection);

                var dataSet = new DataSet();
                adapter.Fill(dataSet, "Results");

                var table = dataSet.Tables[0];
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    var row = table.Rows[i];
                    Console.WriteLine("nazwa: {0}, cena: {1}", row[1], row[2]);   
                }
                // wypisuje na ekran:
                // nazwa: Apple, cena: 12,2
                // nazwa: Orange, cena: 18
            }
        }

        private static void QueryProductPriceDataReader()
        {
            using (var connection = new JulaDbConnection(@"Data Source=C:\Databases\sample.juladb"))
            {
                connection.Open();
                var command = new JulaDbCommand(connection);
                command.CommandText = "select * from Product where Price > 10 and Price < 20";
                var reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine("nazwa: {0}, cena: {1}",
                        reader.GetString(1), reader.GetDecimal(2));
                }
                // wypisuje na ekran:
                // nazwa: Apple, cena: 12,2
                // nazwa: Orange, cena: 18
            }
        }

        /*
        private static JulaDbConnection _connection;

        private static void AddCompany(int id, string name)
        {
            TestUtils.ExecuteValidNonQuery(_connection,
                String.Format("insert into company (Id, Name) values ({0}, '{1}')", id, name));
        }

        static void Main(string[] args)
        {
            _connection = TestUtils.CreateTestDatabase();
            _connection.ExecuteValidNonQuery("create table company (id int, name varchar(100))");
            var w = new Stopwatch();
            w.Start();
            for (int i = 0; ; i++)
            {
                var name = "company" + i;
                AddCompany(i, name);

                if (i % 10000 == 0)
                {
                    var span = w.Elapsed;
                    Console.WriteLine("{0}: added {1}", span, name);
                }
            }
        }
        */

        static void Main(string[] args)
        {
            if (File.Exists(@"C:\Databases\sample.juladb"))
                File.Delete(@"C:\Databases\sample.juladb");

            CreateSampleSchema();
            FillSampleSchema();
            QueryCountRows();
            QueryProductPriceDataReader();
            Console.ReadLine();
        }
    }
}
